package org.ssp.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ResourceBundle;

import org.ssp.base.ResultSetHanlder;
import org.ssp.pojo.School;


/**
 *  DAO--数据访问对象
 *  domain/dao接口/impl实现类
 *  
 *  
 *  http request----servlet---service(业务逻辑)---dao--dbutil工具类
 *  
 *  
 *  dao优化：
 *  1)增删改优化
 *  2)查询优化
 *  
 *  
 *  
 *  
 *  策略设计模式：
 *   把可变的东西抽象出来形成算法簇
 *   具体的实现交给实现类处理
 *   
 *   
 * @author susp
 *
 */
public class DBUtil {
	private static String driver;
	private static String url;
	private static String name;
	private static String password;
	private static ResourceBundle rb=ResourceBundle.getBundle("db");

	
	static{
		//读取配置文件
		driver=rb.getString("jdbc.driver");
		url=rb.getString("jdbc.url");
		name=rb.getString("jdbc.name");
		password=rb.getString("jdbc.password");

/*		//加载驱动
		try {
			Class.forName(driver);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("加载驱动失败");
		}*/
	}
	//获取链接
	public static Connection getConnetion(){
		Connection conn=null;
		try {
			Class.forName(driver);
			System.out.println("驱动加载成功");
			conn=DriverManager.getConnection(url, name, password);
			System.out.println("连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("连接失败");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("驱动加载失败");
		}
		return conn;
	}
	//释放资源
	public static void RelaseSource(ResultSet rs,Statement sm,Connection conn ){
		try {
			if(rs!=null){
			   rs.close();	
			}
			if(sm!=null){
				sm.close();
			}
			if(conn!=null){
				conn.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//抽象增删改,可变参数的使用
	public static void update(String sql,Object...args){
		Connection conn=null;
		PreparedStatement  ps=null;
		try {
			conn=getConnetion();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1, args[i]);
				}
			}
			ps.executeUpdate();
			System.out.println("更新成功");
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}finally{
			RelaseSource(null, ps, conn);
		}
		
	}
	
	//抽象增删改,可变参数的使用
		public static int update2(String sql1,String sql2,Object...args){
			Integer id=null;
			Connection conn=null;
			PreparedStatement  ps=null;
			try {
				conn=getConnetion();
				ps=conn.prepareStatement(sql1);
				if(args!=null){
					for(int i=0;i<args.length;i++){
						ps.setObject(i+1, args[i]);
					}
				}
				ps.executeUpdate();
				System.out.println("更新成功");
				ps=conn.prepareStatement(sql2);
				ResultSet rs=ps.executeQuery();
				if(rs.next()){
					id=rs.getInt(1);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}finally{
				RelaseSource(null, ps, conn);
			}
			return id;
		}
		public static int update3(String sql,Object...args){
			Connection conn=null;
			PreparedStatement  ps=null;
			int count=0;
			try {
				conn=getConnetion();
				ps=conn.prepareStatement(sql);
				if(args!=null){
					for(int i=0;i<args.length;i++){
						ps.setObject(i+1, args[i]);
					}
				}
				count=ps.executeUpdate();
				System.out.println("更新成功");
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return 0;
			}finally{
				RelaseSource(null, ps, conn);
			}
			return count;
		}
	//抽象查询,可变参数的位置必须放在参数列表的最后,策略设计模式的使用,接口的多态
	//策略设计模式，把如何处理抽象为接口,接口编程
	//模板设计模式，可变的部分使用策略模式延迟到子类处理
	public static Object query(String sql,ResultSetHanlder rsh,Object...args){
		PreparedStatement  ps=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn=getConnetion();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1, args[i]);
				}
			}
			rs=ps.executeQuery();
			return rsh.doHandler(rs);
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}finally{
			RelaseSource(null, ps, conn);
		}
	}
	//查询记录数
	public static int count(String sql,Object...args){
		Connection conn=null;
		PreparedStatement  ps=null;
		ResultSet rs=null;
		int counts=0;
		try {
			conn=getConnetion();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1, args[i]);
				}
			}
			rs=ps.executeQuery();
			if(rs.next()){
				counts++;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return 0;
		}finally{
			RelaseSource(null, ps, conn);
		}
		return counts;
	}
	//创建table
	public static void createTable(String sql){
		Connection conn=getConnetion();
		PreparedStatement ps=null;
		try {
			 ps=conn.prepareStatement(sql);
			 ps.execute();
			 System.out.println("创建表格成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("创建表格失败"+e.getMessage());
		}finally {
			RelaseSource(null, ps, conn);
		}
	}
}
